/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ems.database;

import ems.lib.Staff;
import ems.lib.Task;
import ems.lib.Utility;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Iterator;
import java.util.List;
import java.sql.Statement;
import java.util.HashMap;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Map;

public class TaskDAO {

    private Connection conn = null;// DBConnection.connect();
    private Utility utility = new Utility();
    private StaffDAO staffDAO = new StaffDAO();
    private PreparedStatement ps = null;
    private String[] resType = {"Police", "Medic", "Firefighter", "Car", "Medical Kit", "Fire Engine"};

    public TaskDAO() {
        this.conn = (DBConnection.getConnection() != null) ? DBConnection.getConnection() : DBConnection.connect();
    }

    /**
     * Add a new task from a task object
     * @param t
     * @return task id generated from the insert. 0 if failed to insert
     */
    public int addTask(Task t) {
        PreparedStatement psInsert = null;
        int newid = 0;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd  hh:mm");
        java.util.Date parsedDate = null;
        try {
            parsedDate = sdf.parse(t.getTimeCreated());
        } catch (ParseException e) {            
            e.printStackTrace();
        }
        java.sql.Timestamp timestamp = new java.sql.Timestamp(parsedDate.getTime());


        try {
            psInsert = conn.prepareStatement("INSERT INTO Task "
                    + "(DESCRIPTION, LOCATION, URGENCY, STATUS, TYPE, TIME_CREATED) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, t.getDescription());
            psInsert.setString(2, t.getLocation());
            psInsert.setInt(3, t.getUrgency());
            psInsert.setString(4, t.getStatus());
            psInsert.setString(5, t.getType());
            psInsert.setTimestamp(6, timestamp);
            psInsert.executeUpdate();
            conn.commit();

            ResultSet resultSet = psInsert.getGeneratedKeys();


            if (resultSet != null && resultSet.next()) {
                newid = resultSet.getInt(1);
            }
            return newid;

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return newid;
    }

    /**
     * Dispatch a task - assign resource and fo
     * @param taskID
     * @param staffID
     * @param lresid
     */
    public void dispatchTask(int taskID, int staffID, List<Integer> lresid) {
        try {
            //Update task table
            ps = conn.prepareStatement("UPDATE task SET STATUS = 'DISPATCHED' WHERE task_id = ?");
            ps.setInt(1, taskID);
            ps.executeUpdate();
            conn.commit();

            //Update staff table
            ps = conn.prepareStatement("UPDATE staff SET status = 'UNAVAILABLE' WHERE staff_id = ?");
            ps.setInt(1, staffID);
            ps.executeUpdate();
            conn.commit();

            //Update the staff task join table
            ps = conn.prepareStatement("INSERT INTO staff_task (staff_id, task_id ) values (? , ?)");
            ps.setInt(1, staffID);
            ps.setInt(2, taskID);
            ps.executeUpdate();
            conn.commit();

            //Update the resource table
            Iterator<Integer> itr = lresid.iterator();

            while (itr.hasNext()) {
                ps = conn.prepareStatement("UPDATE resource SET status = 'UNAVAILABLE' WHERE resource_id = ?");
                ps.setInt(1, itr.next().intValue());
                ps.executeUpdate();
                conn.commit();
                ps = conn.prepareStatement("INSERT INTO task_resource (task_id,resource_id)  values (?, ?)");
                ps.setInt(1, taskID);
                ps.setInt(2, itr.next().intValue());
                ps.executeUpdate();
                conn.commit();
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /**
     * AutoDispatch a task by the system
     * @param taskID
     * @return success or failure
     */
    public String autoDispatchTask(int taskID) {
        String result =  "Can't auto dispatch task. Error occurred!";
        try {
            HashMap<String, Integer> hm = getResourceRequired(taskID);
            Iterator iter = hm.entrySet().iterator();
            String resType = "";
            String resNeed = "";

            // Assign Resource
            while (iter.hasNext()) {
                Map.Entry mEntry = (Map.Entry) iter.next();
                resType = mEntry.getKey().toString();
                resNeed = mEntry.getValue().toString();

                if (Integer.parseInt(resNeed) < 1) {
                    continue;
                }
                ps = conn.prepareStatement("SELECT resource_id FROM resource "
                        + "WHERE status = 'AVAILABLE' AND resource = ?");
                
                ps.setMaxRows(Integer.parseInt(resNeed));                
                ps.setString(1, resType);
                
                ResultSet rs = ps.executeQuery();
                PreparedStatement psUpdate = conn.prepareStatement("INSERT INTO "
                        + "task_resource (task_id,resource_id)  values (?, ?)");
                PreparedStatement psUpdate2 = conn.prepareStatement("UPDATE " +
                        "resource SET status = 'UNAVAILABLE' WHERE resource_id = ?");
                while (rs.next()) {
                    psUpdate.setInt(1, taskID);
                    psUpdate.setInt(2, rs.getInt("resource_id"));
                    psUpdate.executeUpdate();
                    conn.commit();
                    psUpdate2.setInt(1, rs.getInt("resource_id"));
                    psUpdate2.executeUpdate();
                    conn.commit();
                }
            }

            // Assign FO
            ps = conn.prepareStatement("SELECT staff_id FROM staff WHERE status='AVAILABLE' AND job='Field Officer'");
            ps.setMaxRows(1);
            int staffID = 0;
            ResultSet rs1 = ps.executeQuery();
            while (rs1.next()) {
                staffID = rs1.getInt("staff_id");
            }


            //Update staff table
            ps = conn.prepareStatement("UPDATE staff SET status = 'UNAVAILABLE' WHERE staff_id = ?");
            ps.setInt(1, staffID);
            ps.executeUpdate();
            conn.commit();

            //Update the staff task join table
            ps = conn.prepareStatement("INSERT INTO staff_task (staff_id, task_id ) values (? , ?)");
            ps.setInt(1, staffID);
            ps.setInt(2, taskID);
            ps.executeUpdate();
            conn.commit();

            //Update task table
            ps = conn.prepareStatement("UPDATE task SET STATUS = 'DISPATCHED' WHERE task_id = ?");
            ps.setInt(1, taskID);
            ps.executeUpdate();
            conn.commit();

            result = "Task " + taskID + " is successfully dispatched automatically with the following details: ";
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    /**
     * Close a task, free up resource and fo
     * @param task
     */
    public void closeTask(Task task) {
        Calendar cal = Calendar.getInstance();
        PreparedStatement psDelete = null;
        PreparedStatement psUpdate = null;
        PreparedStatement get = null;
        int id = task.getTaskId();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd  hh:mm");


        java.sql.Timestamp timestamp = null;
        try {
            timestamp = new java.sql.Timestamp(sdf.parse(sdf.format(cal.getTime())).getTime());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        try {

            //Free up staff
            get = conn.prepareStatement("SELECT Staff_id FROM staff_task WHERE task_id = ?");
            get.setInt(1, id);
            ResultSet rs = get.executeQuery();

            //Change staff status
            psUpdate = conn.prepareStatement("UPDATE staff SET status = 'AVAILABLE' WHERE staff_id = ?");
            while (rs.next()) {
                psUpdate.setInt(1, rs.getInt("STAFF_ID"));
                psUpdate.executeUpdate();
                conn.commit();
            }

            //Delete task from staff_task
            psDelete = conn.prepareStatement("DELETE FROM Staff_Task where task_id = ?");
            psDelete.setInt(1, id);
            psDelete.executeUpdate();
            conn.commit();

            //Change status of the individually assigned resources
            ps = conn.prepareStatement("SELECT resource_id FROM task_resource WHERE task_id = ?");
            ps.setInt(1, id);
            rs = ps.executeQuery();
            psUpdate = conn.prepareStatement("UPDATE resource SET status = 'AVAILABLE' WHERE resource_id = ?");
            while (rs.next()) {
                psUpdate.setInt(1, rs.getInt("resource_id"));
                psUpdate.executeUpdate();
                conn.commit();
            }

            //Remove required resources
            psDelete = conn.prepareStatement("DELETE FROM TASK_RESREQUIRE WHERE task_id = ?");
            psDelete.setInt(1, id);
            psDelete.executeUpdate();
            conn.commit();

            psDelete = conn.prepareStatement("DELETE FROM TASK_RESOURCE WHERE task_id = ?");
            psDelete.setInt(1, id);
            psDelete.executeUpdate();
            conn.commit();

            //Update task table            
            psUpdate = conn.prepareStatement("UPDATE task SET STATUS = 'CLOSED', TIME_CLOSED = ? WHERE task_id = ?");
            System.out.println("UPDATE task SET STATUS = 'CLOSED', TIME_CLOSED = " + timestamp + " WHERE task_id = " + id);
            psUpdate.setTimestamp(1, timestamp);
            psUpdate.setInt(2, id);
            psUpdate.executeUpdate();
            conn.commit();
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /**
     * Set the amount of resource required
     * @param id
     * @param ls
     */
    public void setResourceRequired(int id, List ls) {
        try {
            int i = 0;
            Iterator itr = ls.iterator();

            while (itr.hasNext()) {
                ps = conn.prepareStatement("INSERT INTO TASK_RESREQUIRE (Task_ID, ResType, Number)"
                        + " values (?, ?, ?)");
                ps.setInt(1, id);
                ps.setString(2, resType[i]);
                ps.setInt(3, Integer.parseInt(itr.next().toString()));
                ps.executeUpdate();
                conn.commit();
                i++;
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }

    /**
     * Updates the required resources for task a task.
     * @param id
     * :<br>Task ID
     * @param ls
     * :<br>List of additional requested resources
     */
    public void updateResourceRequired(int id, List<Integer> ls) {
        PreparedStatement get = null;
        PreparedStatement psUpdate = null;
        try {
            int i = 0;
            int number;
            int newNum;
            Iterator itr = ls.iterator();

            while (itr.hasNext()) {

                //Get current required resources
                get = conn.prepareStatement("SELECT number FROM task_resrequire WHERE Task_ID = ? AND ResType = ? ");
                get.setInt(1, id);
                get.setString(2, resType[i]);
                ResultSet rs = get.executeQuery();
                number = rs.getInt("number");

                //Add addtional
                newNum = Integer.parseInt(itr.next().toString());
                newNum = newNum + number;

                //update
                psUpdate = conn.prepareStatement("UPDATE task_resrequire SET number = ? WHERE Task_ID = ? AND ResType = ?");
                psUpdate.setInt(1, newNum);
                psUpdate.setInt(2, id);
                psUpdate.setString(3, resType[i]);
                psUpdate.executeUpdate();
                conn.commit();

                i++;


            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

    }

    /**
     * Get the resource required for the task
     * @param id
     * @return hashmap of required resource
     */
    public HashMap getResourceRequired(int id) {
        HashMap hm = new HashMap();
        try {
            ps = conn.prepareStatement("SELECT * FROM TASK_RESREQUIRE WHERE "
                    + "Task_ID = ?");
            //(Task_ID, ResType, Number)
            ps.setInt(1, id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                hm.put(resultSet.getString("ResType"), resultSet.getInt("Number"));
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return hm;
    }

    /**
     * Get the resource allocated for the task
     * @param id
     * @return hashmap of allocated resources
     */
    public HashMap getResourceAllocated(int id) {
        HashMap hm = new HashMap();
        String tmp = "";
        try {
            ps = conn.prepareStatement("SELECT * FROM resource WHERE resource_id " +
                    "IN (SELECT resource_id FROM task_resource WHERE task_id = ?)" +
                    " ORDER BY resource");
            ps.setInt(1, id);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                tmp = resultSet.getString("Resource") + " " + resultSet.getInt("Resource_No");
                hm.put(tmp, "[ID " + resultSet.getString("resource_id") + "]");
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return hm;
    }

    /**
     * Get a list of all tasks in the system
     * @return a list of all tasks
     */
    public List<Task> getAllTask(String filter) {
        //PreparedStatement ps = null;
        List<Task> taskList = new ArrayList<Task>();
        //List<Staff> staffList = new ArrayList<Staff>();
        String status = "";


        try {
            status = (filter.isEmpty()) ? " " : " WHERE status = '" + filter.toUpperCase() + "' ";
            ps = conn.prepareStatement("SELECT * FROM Task t " + status  + " ORDER BY Urgency DESC");
            ResultSet resultSet = ps.executeQuery();


            //int taskid;


            while (resultSet.next()) {
                //taskid = resultSet.getInt("TASK_ID");
               // staffList = this.getStaffByTask(taskid);
                Task t = new Task(resultSet.getInt("Task_Id"), resultSet.getString("Type"),
                        resultSet.getString("Description"),
                        resultSet.getString("Location"), resultSet.getInt("Urgency"),
                        resultSet.getString("Status"), resultSet.getString("time_created"),
                        resultSet.getString("time_closed"));
                taskList.add(t);


            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();


        }
        return taskList;


    }

    /**
     * Get a list of all tasks in the system
     * @return a list of all tasks
     */
    public List<Task> getTask(int tid) {
        List<Task> taskList = new ArrayList<Task>();
        //List<Staff> staffList = new ArrayList<Staff>();

        try {
            ps = conn.prepareStatement("SELECT * FROM Task t WHERE task_id = ?");
            ps.setInt(1, tid);
            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                //staffList = this.getStaffByTask(taskid);

                Task t = new Task(tid, resultSet.getString("Type"),
                        resultSet.getString("Description"),
                        resultSet.getString("Location"), resultSet.getInt("Urgency"),
                        resultSet.getString("Status"), null, null);
                taskList.add(t);
            }

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return taskList;
    }

    /**
     * Get a list of staff (Field Officer)
     * @param taskid
     * @return list of staff (Field Officer)
     */
    public List<Staff> getStaffByTask(int taskid) {
        int staffid;
        List<Staff> staffList = new ArrayList<Staff>();

        try {
            ps = conn.prepareStatement("SELECT STAFF_ID FROM Staff_Task st WHERE st.task_id = ?");
            ps.setInt(1, taskid);
            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                staffid = resultSet.getInt("STAFF_ID");
                staffList.add(staffDAO.getStaff(taskid));
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return staffList;
    }

    //edited by JJ - Removed while look and initialised t as null, so if task does not exist it will return null.
    public Task getTaskByStaff(Staff staff) {
        Task t = null;

        try {
            ps = conn.prepareStatement("SELECT * FROM Task t "
                    + "INNER JOIN Staff_Task st ON t.TASK_ID = st.TASK_ID "
                    + "WHERE st.staff_id =?");
            ps.setInt(1, staff.getStaffId());
            ResultSet resultSet = ps.executeQuery();

            if (resultSet.next()) {

                t = new Task(resultSet.getInt("Task_Id"), resultSet.getString("type"),
                        resultSet.getString("Description"),
                        resultSet.getString("Location"), resultSet.getInt("Urgency"),
                        resultSet.getString("Status"), null, null);
            }
            return t;
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return null;
    }

    /**
     * Get all tasks and their by staffID
     * @param staffID
     * @return 2 dimensional array Object
     */
    public Object[][] getTaskByStaff(int staffID) {
        Object[][] data = {};



        try {

            ps = conn.prepareStatement("SELECT * FROM Task t, Staff_Task st WHERE st.staff_id = ?");
            ps.setInt(1, staffID);
            ResultSet resultSet = ps.executeQuery();
            data = utility.resultSetToObjectArray(resultSet);



        } catch (SQLException sqle) {
            sqle.printStackTrace();


        }
        return data;


    }

    public static void main(String[] Args) {
        DBConnection.connect();

        TaskDAO t = new TaskDAO();
        List list = new ArrayList();
        list = t.getAllTask("");
        Iterator<Task> itr = list.iterator();



        while (itr.hasNext()) {
            Task element = itr.next();
            System.out.println("cc " + element.getDescription() + " "
                    + element.getStaffs().get(0).getUsername());


        }
        DBConnection.disconnect();

    }
}
